/**
 * $version: 0.1 $Date: 2011-01-21
 *
 * Copyright (C) 2010-2012 Jawa Software. All rights reserved.
 *
 */
package org.jawa.core.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.lang.StringUtils;
import org.jawa.core.database.DbConnectionManager;
import org.jawa.core.util.support.FastQueryRunner;
import org.jawa.core.util.support.PagingBeanProcessor;
import org.jawa.core.util.support.PagingMapListHandler;
import org.jawa.core.util.support.PagingRowProcessor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * 数据库操作公用类。 <p> 本方法提供对数据库的常用操作，但是性能并不是非常高(在大数据量下性能尤其低)。
 * 对于关键性操作（比如查询一个非常大的表），建议手工编写代码实现，而不是使用本方法 。 </p>
 *
 * @author Y-N
 *
 */
public class JdbcUtils {

    private static final Logger Log = LoggerFactory.getLogger(JdbcUtils.class);
    // 在数据库布尔值'真'的字符串表示值
    public static final String DEFAULT_DATABASE_TRUE_VALUE = "T";
    // 在数据库布尔值'假'的字符串表示值
    public static final String DEFAULT_DATABASE_FALSE_VALUE = "F";

    /**
     * 执行一个数据库的更新操作。
     *
     * @param con 数据库连接
     * @param sql 要执行的数据库语句
     * @param params 数据库语句中包含的参数值
     * @return 更新受影响的行数
     * @throws SQLException
     */
    public static int executeUpdate(Connection con, String sql,
            Object... params) throws SQLException {
        QueryRunner run = new QueryRunner();
        int rows = 0;
        try {
            rows = run.update(con, sql, params);
        } catch (SQLException e) {
            rethrow(e, sql, params);
        } finally {
            // 不关闭传进来的数据库连接.
        }
        return rows;
    }

    /**
     * 执行一个数据库的更新操作。
     *
     * @param sql 要执行的数据库语句
     * @param params 数据库语句中包含的参数值
     * @return 更新受影响的行数
     * @throws SQLException
     */
    public static int executeUpdate(String sql, Object... params)
            throws SQLException {
        Connection con = null;
        int rows = 0;
        try {
            con = getConnection();
            rows = executeUpdate(con, sql, params);
        } catch (SQLException e) {
            throw e;
        } finally {
            closeConnection(con);
        }
        return rows;
    }

    /**
     * 执行批量的数据库预编译语句。
     *
     * <p> 例如： </p>
     *
     * <pre>
     * JdbcUtils.batch(&quot;INSERT INTO ROLE(ROLE_ID,ROLENAME) VALUES(?,?);&quot;,
     * 	new Object[][] { { -11, &quot;A&quot; }, { -21, &quot;B&quot; } })
     * </pre>
     *
     * @param con 数据库连接
     * @param sql 批量的数据库 INSERT, UPDATE, DELETE 预编译语句
     * @param params 批量数据库语句的参数值，数组的的每一行做一个语句的替换 </b>。
     * @return 更新受影响的行数
     * @throws SQLException
     */
    public static int[] batch(Connection con, String sql, Object[][] params)
            throws SQLException {
        QueryRunner run = new QueryRunner();
        try {
            return run.batch(con, sql, params);
        } finally {
            // 不关闭传进来的数据库连接.
        }

    }

    /**
     * 执行批量的数据库预编译语句。
     *
     * <p> 例如： </p>
     *
     * <pre>
     * JdbcUtils.batch(&quot;INSERT INTO ROLE(ROLE_ID,ROLENAME) VALUES(?,?);&quot;,
     * 	new Object[][] { { -11, &quot;A&quot; }, { -21, &quot;B&quot; } })
     * </pre>
     *
     * @param sql 批量的数据库 INSERT, UPDATE, DELETE 预编译语句
     * @param params 批量数据库语句的参数值，数组的的每一行做一个语句的替换 </b>。
     * @return 更新受影响的行数
     * @throws SQLException
     */
    public static int[] batch(String sql, Object[][] params)
            throws SQLException {
        Connection con = null;
        try {
            con = getConnection();
            return batch(con, sql, params);
        } finally {
            closeConnection(con);
        }

    }

    /**
     * 将一个 Bean 插入到数据库表中。 <p> 根据 Bean 中可访问的属性名称以及 Bean 的访问器方法得到 Bean
     * 的属性值生成插入语句，插入指定名称的数据表中。 <p>
     *
     * @param con 数据库连接
     * @param tableName 数据表名称
     * @param entityObj Bean 对象
     * @param ignoredColumns 要忽略的 Bean 属性名称
     * @return 更新受影响的行数.
     * @throws SQLException
     */
    public static int insertTableFromBean(Connection con, String tableName,
            Object entityObj, String... ignoredColumns) throws SQLException {
        int rows = 0;
        StringBuffer insert = new StringBuffer("INSERT INTO " + tableName
                + " ( ");
        StringBuffer values = new StringBuffer();
        PreparedStatement pstmt = null;
        boolean anyKeys = false;
        try {
            Map<?, ?> beanDescribe = new HashMap<String, Object>();
            try {
                beanDescribe = PropertyUtils.describe(entityObj);
            } catch (Exception e) {
                Log.error("获取 Bean '" + entityObj.getClass() + "' 属性时发生错误!");
            }
            Set<?> keySet = beanDescribe.keySet();
            for (Iterator<?> iter = keySet.iterator(); iter.hasNext();) {
                String propertyName = (String) iter.next();
                Object propertyValue = beanDescribe.get(propertyName);

                if ("class".equalsIgnoreCase(propertyName)) {
                    continue;
                }

                // 如果没有值，插入时忽略
                if (null == propertyValue) {
                    continue;
                }

                boolean ignoredColumn = false;
                for (int i = 0; i < ignoredColumns.length; i++) {
                    if (ignoredColumns[i].equalsIgnoreCase(propertyName)) {
                        ignoredColumn = true;
                        break;
                    }
                }
                // 如果列名在忽略的属性名称中，则跳过.
                if (ignoredColumn) {
                    continue;
                }

                if (anyKeys) {
                    insert.append(',');
                    values.append(',');
                }
                insert.append(propertyName);
                if (propertyValue instanceof String) {

                    values.append(" '"
                            + StringUtils.trimToEmpty(ObjectUtils.toString(propertyValue)) + "' ");

                } else if (propertyValue instanceof Boolean) {
                    Boolean booleanPropertyValue = Boolean.valueOf(propertyValue.toString());
                    if (booleanPropertyValue) {
                        values.append(" '" + DEFAULT_DATABASE_TRUE_VALUE + "' ");
                    } else {
                        values.append(" '" + DEFAULT_DATABASE_FALSE_VALUE
                                + "' ");
                    }
                } else if (propertyValue instanceof Number) {
                    values.append(propertyValue);
                } else {
                    values.append(ObjectUtils.toString(propertyValue));
                }
                anyKeys = true;
            }

            if (anyKeys) {
                insert.append(")");
                insert.append(" VALUES ");
                insert.append(" ( ");
                insert.append(values);
                insert.append(")");
                pstmt = con.prepareStatement(insert.toString());
                rows = pstmt.executeUpdate();
            }
        } catch (SQLException e) {
            rethrow(e, insert.toString(), (Object[]) null);
        } finally {
            closeStatement(pstmt);
            // 不关闭传进来的数据库连接.
        }
        return rows;
    }

    /**
     * 将一个 Bean 插入到数据库表中。 <p> 根据 Bean 中可访问的属性名称以及 Bean 的访问器方法得到 Bean
     * 的属性值生成插入语句，插入指定名称的数据表中。 <p>
     *
     * @param tableName 数据表名称
     * @param entityObj Bean 对象
     * @param ignoredColumns 要忽略的 Bean 属性名称
     * @return 更新受影响的行数.
     * @throws SQLException
     */
    public static int insertTableFromBean(String tableName, Object entityObj,
            String... ignoredColumns) throws SQLException {
        Connection con = null;
        int rows = 0;
        try {
            con = getConnection();
            rows = insertTableFromBean(con, tableName, entityObj,
                    ignoredColumns);
        } catch (SQLException e) {
            throw e;
        } finally {
            closeConnection(con);
        }
        return rows;
    }

    /**
     * 将一个 Bean 更新到数据库表中。 <p> 根据 Bean 中可访问的属性名称以及 Bean 的访问器方法得到 Bean
     * 的属性值生成更新语句，更新指定名称的数据表中。 <p>
     *
     * @param con 数据库连接
     * @param tableName 表名
     * @param entityObj Bean 对象
     * @param whereClause 更新的条件
     * @param ignoreNull 是否忽略 Bean 属性为 null 的字段
     * @param ignoredColumns 要忽略的 Bean 属性名称.
     * @return 更新受影响的行数.
     *
     * @throws Exception
     */
    public static int updateTableFromBean(Connection con, String tableName,
            Object entityObj, String whereClause, boolean ignoreNull,
            String... ignoredColumns) throws SQLException {
        int rows = 0;
        StringBuffer updateSql = new StringBuffer("UPDATE  " + tableName
                + " SET  ");
        PreparedStatement pstmt = null;
        boolean anyKeys = false;
        try {
            Map<?, ?> beanDescribe = new HashMap<String, Object>();
            try {
                beanDescribe = PropertyUtils.describe(entityObj);
            } catch (Exception e) {
                Log.error("获取 Bean '" + entityObj.getClass() + "' 属性时发生错误!");
            }
            Set<?> keySet = beanDescribe.keySet();
            for (Iterator<?> iter = keySet.iterator(); iter.hasNext();) {
                String propertyName = (String) iter.next();
                Object propertyValue = beanDescribe.get(propertyName);

                if ("class".equalsIgnoreCase(propertyName)) {
                    continue;
                }

                // 忽略 entityObj 属性值为 null 的字段
                if (ignoreNull && null == propertyValue) {
                    continue;
                }

                boolean ignoredColumn = false;
                for (int i = 0; i < ignoredColumns.length; i++) {
                    if (ignoredColumns[i].equalsIgnoreCase(propertyName)) {
                        ignoredColumn = true;
                        break;
                    }
                }
                // 如果列名在忽略的属性名称中，则跳过.
                if (ignoredColumn) {
                    continue;
                }

                if (anyKeys) {
                    updateSql.append(',');
                }

                updateSql.append(propertyName);
                if (propertyValue instanceof String) {
                    updateSql.append(" = '"
                            + StringUtils.trimToEmpty(ObjectUtils.toString(propertyValue)) + "' ");
                } else if (propertyValue instanceof Boolean) {
                    Boolean booleanPropertyValue = Boolean.valueOf(propertyValue.toString());
                    if (booleanPropertyValue) {
                        updateSql.append(" = '" + DEFAULT_DATABASE_TRUE_VALUE
                                + "' ");
                    } else {
                        updateSql.append(" ='" + DEFAULT_DATABASE_FALSE_VALUE
                                + "' ");
                    }
                } else if (propertyValue instanceof Number) {
                    updateSql.append(" = " + propertyValue);
                } else {
                    updateSql.append(" = '"
                            + ObjectUtils.toString(propertyValue) + "' ");
                }
                anyKeys = true;
            }

            if (anyKeys) {
                if (StringUtils.isNotEmpty(whereClause)) {
                    updateSql.append(" WHERE " + whereClause);
                }
                pstmt = con.prepareStatement(updateSql.toString());
                pstmt.executeUpdate();
            }
        } catch (SQLException e) {
            rethrow(e, updateSql.toString(), (Object[]) null);
        } finally {
            closeStatement(pstmt);
            // 不关闭传进来的数据库连接.
        }
        return rows;
    }

    /**
     * 将一个 Bean 更新到数据库表中。 <p> 根据 Bean 中可访问的属性名称以及 Bean 的访问器方法得到 Bean
     * 的属性值生成更新语句，更新指定名称的数据表中。 <p>
     *
     * @param tableName 表名
     * @param entityObj Bean 对象
     * @param whereClause 更新的条件
     * @param ignoreNull 是否忽略 Bean 属性为 null 的字段
     * @param ignoredColumns 要忽略的 Bean 属性名称.
     * @return 更新受影响的行数.
     *
     * @throws Exception
     */
    public static int updateTableFromBean(String tableName, Object entityObj,
            String whereClause, boolean ignoreNull, String... ignoredColumns)
            throws SQLException {
        Connection con = null;
        int rows = 0;
        try {
            con = getConnection();
            rows = updateTableFromBean(con, tableName, entityObj, whereClause,
                    ignoreNull, ignoredColumns);
        } catch (SQLException e) {
            throw e;
        } finally {
            closeConnection(con);
        }
        return rows;
    }

    /**
     * 根据查询语句查询数据库表中的一条记录，并将记录写入 Bean 中。
     *
     * <p> 根据 Bean 中可访问的属性名称以及 Bean 的设定器方法将数据表中数据写入Bean。 <p>
     *
     * @param entityClass Bean Class
     * @param sql 查询语句
     * @param params 查询语句中的参数
     * @return Bean
     * @throws SQLException
     */
    public static <T> T queryToBean(Class<T> entityClass, String sql,
            Object... params) throws SQLException {
        QueryRunner run = new QueryRunner();
        Connection con = null;
        T bean = null;
        try {
            con = getConnection();
            bean = run.query(con, sql, new BeanHandler<T>(entityClass), params);
        } catch (SQLException e) {
            rethrow(e, sql, (Object[]) null);
        } finally {
            closeConnection(con);
        }
        return bean;
    }

    /**
     * 根据查询语句查询数据库表中的一条记录，并将记录写入 Map 中。
     *
     * <p> 将数据表中数据写入 Map 中,以数据表中列名做为 key (在使用时大小写key 键都可以使用)键，以数据值做为 Value 。 <p>
     *
     * @param sql 查询语句
     * @param params 查询语句中的参数
     * @return Map
     * @throws SQLException
     */
    public static Map<String, Object> queryToMap(String sql, Object... params)
            throws SQLException {
        QueryRunner run = new QueryRunner();
        Connection con = null;
        Map<String, Object> bean = null;
        try {
            con = getConnection();
            bean = run.query(con, sql, new MapHandler(), params);
        } catch (SQLException e) {
            rethrow(e, sql, (Object[]) null);
        } finally {
            closeConnection(con);
        }
        return bean;
    }

    /**
     * 执行查询，将每行的结果保存到 Bean 中，然后将所有 Bean 封装入 List返回。
     *
     * @param con 数据库连接
     * @param entityClass Bean Class
     * @param sql 查询语句
     * @param params 查询语句中的参数
     * @return List<T> 查询结果
     * @throws SQLException
     */
    public static <T> List<T> queryToBeanList(Connection con,
            Class<T> entityClass, String sql, Object... params)
            throws SQLException {
        List<T> list = new ArrayList<T>();
        QueryRunner run = new QueryRunner();
        try {
            list = run.query(con, sql, new BeanListHandler<T>(entityClass),
                    params);
        } catch (SQLException e) {
            throw e;
        } finally {
            // 不关闭传入的连接
        }
        return list;
    }

    /**
     * 执行查询，将每行的结果保存到 Bean 中，然后将所有 Bean 封装入 List返回。
     *
     * @param entityClass Bean Class
     * @param sql 查询语句
     * @param params 查询语句中的参数
     * @return List<T> 查询结果
     * @throws SQLException
     */
    public static <T> List<T> queryToBeanList(Class<T> entityClass, String sql,
            Object... params) throws SQLException {
        Connection con = null;
        try {
            con = getConnection();
            return queryToBeanList(con, entityClass, sql, params);
        } catch (SQLException e) {
            throw e;
        } finally {
            closeConnection(con);
        }
    }

    /**
     * 执行分页查询，将每行的结果保存到Bean中，然后将所有Bean保存到List中。
     *
     * @param entityClass Bean Class
     * @param sql 查询语句
     * @param page 分页对象
     * @param params 查询语句中的参数
     * @return List<T> 查询结果
     * @throws SQLException
     */
    public static <T> List<T> queryToBeanListWithPaging(Class<T> entityClass,
            String sql, int startIndex, int numResults, Object... params)
            throws SQLException {
        List<T> list = new ArrayList<T>();
        // 在此处需要使用可滚动的Statement可提高性能。
        QueryRunner run = new FastQueryRunner();
        Connection con = null;
        try {
            con = getConnection();
            list = run.query(con, sql, new BeanListHandler<T>(entityClass,
                    new PagingRowProcessor(new PagingBeanProcessor(startIndex,
                    numResults))), params);
        } catch (SQLException e) {
            rethrow(e, sql, params);
        } finally {
            closeConnection(con);
        }
        return list;
    }

    /**
     * 执行分页查询，将每行的结果保存到Map中，然后将所有Map保存到List中。
     *
     * <p> 将数据表中数据写入 Map 中,以数据表中列名做为 key (在使用时大小写key 键都可以使用)键，以数据值做为 Value 。 <p>
     *
     * @param con 数据库连接
     * @param sql 查询语句
     * @param params 查询语句中的参数
     * @return List<T> 查询结果
     * @throws SQLException
     */
    public static List<Map<String, Object>> queryToMapList(Connection con,
            String sql, Object... params) throws SQLException {
        QueryRunner run = new QueryRunner();
        List<Map<String, Object>> list = null;
        try {
            list = run.query(con, sql, new MapListHandler(), params);
        } catch (SQLException e) {
            rethrow(e, sql, params);
        } finally {
            // 不关闭传入的连接
        }
        return list;
    }

    /**
     * 执行分页查询，将每行的结果保存到Map中，然后将所有Map保存到List中。
     *
     * <p> 将数据表中数据写入 Map 中,以数据表中列名做为 key (在使用时大小写key 键都可以使用)键，以数据值做为 Value 。 <p>
     *
     * @param sql 查询语句
     * @param params 查询语句中的参数
     * @return List<T> 查询结果
     * @throws SQLException
     */
    public static List<Map<String, Object>> queryToMapList(String sql,
            Object... params) throws SQLException {
        Connection con = null;
        List<Map<String, Object>> list = null;
        try {
            con = getConnection();
            list = queryToMapList(con, sql, params);
        } catch (SQLException e) {
            throw e;
        } finally {
            closeConnection(con);
        }
        return list;
    }

    /**
     * 执行查询，将每行的结果保存到Map中，然后将所有Map保存到List中。
     *
     * <p> 将数据表中数据写入 Map 中,以数据表中列名做为 key (在使用时大小写key 键都可以使用)键，以数据值做为 Value 。 <p>
     *
     * @param sql 查询语句
     * @param page 分页对象
     * @param params 查询语句中的参数
     * @return List<T> 查询结果
     * @throws SQLException
     */
    public static List<Map<String, Object>> queryToMapListWithPaging(
            String sql, int startIndex, int numResults, Object... params)
            throws SQLException {
        // 在此处需要使用可滚动的Statement可提高性能。
        QueryRunner run = new FastQueryRunner();
        Connection con = null;
        List<Map<String, Object>> list = null;
        try {
            con = getConnection();
            list = run.query(con, sql, new PagingMapListHandler(startIndex,
                    numResults), params);
        } catch (SQLException e) {
            rethrow(e, sql, params);
        } finally {
            closeConnection(con);
        }
        return list;
    }

    /**
     *
     * 统计结果集的行数.
     *
     * @param sql 数据库查询语句
     * @return 数据集行数
     * @throws SQLException
     */
    public static int countSqlRowNumber(String sql) throws SQLException {
        int rows = 0;
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            sql = removeOrders(sql);
            con = getConnection();
            if (DbConnectionManager.isSubqueriesSupported()) {
                stmt = con.createStatement();
                rs = stmt.executeQuery("SELECT COUNT(*) CNT FROM ( " + sql
                        + " ) J ");
                if (rs.next()) {
                    rows = rs.getInt("CNT");
                }
            } else {
                stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_READ_ONLY);
                rs = stmt.executeQuery(sql);
                rs.last();
                rows = rs.getRow();
            }
        } catch (SQLException e) {
            rethrow(e, sql, (Object[]) null);
        } finally {
            closeConnection(rs, stmt, con);
        }
        return rows;
    }

    /**
     * 返回表的下一个主键.
     *
     * @param tableName 表名
     * @param PKColumnName 表的主键列名
     * @return
     */
    public static Long getTableNextPK(String tableName, String PKColumnName) {
        Long id = 0L;
        StringBuffer sql = new StringBuffer("SELECT MAX(" + PKColumnName
                + ")+1 ID FROM " + tableName);
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            con = getConnection();
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql.toString());
            if (rs.next()) {
                id = rs.getLong("id");
            }
        } catch (Exception ex) {
            // 忽略
            Log.error("生成表'" + tableName + "' 的下一个主键'" + PKColumnName + "'出错!",
                    ex);
        } finally {
            closeConnection(rs, stmt, con);
        }
        return id;
    }

    /**
     * ********* 以下方法为本类的辅助方法，不对外开放 **********
     */
    /**
     * 抛出一个具有更丰富错误信息的新的例外。
     *
     * @param cause 原来发生的异常将被链接到新的异常重新抛出
     * @param sql 发生异常的数据库语句
     * @param params 数据库语句中的参数
     * @return SQLException 数据库访问错误
     */
    private static void rethrow(SQLException cause, String sql,
            Object... params) throws SQLException {
        String causeMessage = cause.getMessage();
        if (causeMessage == null) {
            causeMessage = "";
        }
        StringBuffer msg = new StringBuffer(causeMessage);

        msg.append(" Query: ");
        msg.append(sql);
        msg.append(" Parameters: ");

        if (params == null) {
            msg.append("[]");
        } else {
            msg.append(Arrays.deepToString(params));
        }

        SQLException e = new SQLException(msg.toString(), cause.getSQLState(),
                cause.getErrorCode());
        e.setNextException(cause);

        throw e;
    }

    /**
     * 去除 orderby 子句
     *
     * @param sql
     * @return
     */
    private static String removeOrders(String sql) {
        Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*",
                Pattern.CASE_INSENSITIVE);
        Matcher m = p.matcher(sql);
        StringBuffer sb = new StringBuffer();
        while (m.find()) {
            m.appendReplacement(sb, "");
        }
        m.appendTail(sb);
        return sb.toString();
    }

    private static Connection getConnection() throws SQLException {
        return DbConnectionManager.getConnection();
    }

    private static void closeResultSet(ResultSet rs) {
        DbConnectionManager.closeResultSet(rs);
    }

    private static void closeStatement(Statement stmt) {
        DbConnectionManager.closeStatement(stmt);
    }

    private static void closeConnection(Connection con) {
        DbConnectionManager.closeConnection(con);
    }

    private static void closeConnection(Statement stmt, Connection con) {
        closeStatement(stmt);
        closeConnection(con);
    }

    private static void closeConnection(ResultSet rs, Statement stmt,
            Connection con) {
        closeResultSet(rs);
        closeStatement(stmt);
        closeConnection(con);
    }

    private JdbcUtils() {
        // 不可实例化.
    }
}
